import numpy as np
import pandas as pd
from clickhouse_client import ClickhoustClient
from datetime import timedelta, datetime

class HourData:
    def __init__(self):
        self.client = ClickhoustClient()


    def gen_update_time_table(self):
        is_warn = False
        data = self.get_table_update_time()
        
        # 获取当前时间
        current_time = pd.Timestamp.now(tz='Asia/Shanghai')
        
        for row in data.itertuples():
            print(f"Table: {row.table}, Data Latest Time: {row.data_lastest_time},type:{type(row.data_lastest_time)} Update Time: {row.update_time}, type:{type(row.update_time)}")
            
            # 将data_lastest_time和update_time转换为pandas Timestamp（带时区）
            if pd.notna(row.data_lastest_time):
                data_time = pd.Timestamp(row.data_lastest_time).tz_localize('Asia/Shanghai') if pd.Timestamp(row.data_lastest_time).tz is None else pd.Timestamp(row.data_lastest_time)
                time_diff_data = (current_time - data_time).total_seconds() / 60  # 转换为分钟
                print(f"  Data Latest Time 距离当前时间: {time_diff_data:.2f} 分钟")
                if time_diff_data > 15:
                    is_warn = True
            
            if pd.notna(row.update_time):
                update_time = pd.Timestamp(row.update_time).tz_localize('Asia/Shanghai') if pd.Timestamp(row.update_time).tz is None else pd.Timestamp(row.update_time)
                time_diff_update = (current_time - update_time).total_seconds() / 60  # 转换为分钟
                print(f"  Update Time 距离当前时间: {time_diff_update:.2f} 分钟")
                if time_diff_update > 15:
                    is_warn = True
        
        title = '1、各个表的最新更新时间'
        if is_warn:
            title = '⚠️1、各个表的最新更新时间'
        
        return [title, data.to_html(), is_warn]

    def gen_days_compare_table(self):
        is_warn = False
        data = self.get_days_compare_hour_data()
        title ='2、最近4天截止当前小时激活用户对比数据表'
        today_cnt=data['cnt'].iloc[-1]
        total_cnt=0
        for i in range(len(data)-1):
            total_cnt+=data['cnt'].iloc[i]
        avg_cnt=total_cnt/(len(data)-1)
        diff_percent=(today_cnt - avg_cnt)/avg_cnt*100 if avg_cnt!=0 else 0
        if diff_percent > 10 or diff_percent < -10:
            title ='⚠️2、最近4天截止当前小时激活用户对比数据表'
            is_warn = True
        content=f"今天数据：{today_cnt},<br>前三天平均数据：{avg_cnt:.2f},<br>差异比例：{diff_percent:.2f}%"
        return [title,content,data.to_html(),is_warn]

    def gen_active_final_hour_table(self):
        is_warn = False
        data = self.get_active_final_hour_data()
        title ='3、最近2天小时数据对比'
        before_2rd_hour_cnt=data['cnt'].iloc[-3].astype(np.int32)
        before_3_hour_total_cnt=0
        for i in range(-6,-3):
            before_3_hour_total_cnt+=data['cnt'].iloc[i]
        avg_cnt=before_3_hour_total_cnt/3
        diff_percent=(before_2rd_hour_cnt - avg_cnt)/avg_cnt*100 if avg_cnt!=0 else 0

        yesterday_2rd_hour_cnt=data['cnt'].iloc[-27]
        yesterday_diff_percent= (before_2rd_hour_cnt - yesterday_2rd_hour_cnt) / yesterday_2rd_hour_cnt * 100 if yesterday_2rd_hour_cnt != 0 else 0
        content=f"当前前2小时数据：{before_2rd_hour_cnt}<br>之前3小时平均数据：{avg_cnt:.2f}<br>差异比例：{diff_percent:.2f}%<br>昨天同比数据：{yesterday_2rd_hour_cnt}<br>差异比例：{yesterday_diff_percent:.2f}%"
        if yesterday_diff_percent > 10 or yesterday_diff_percent < -10:
            title ='⚠️3、最近2天小时数据对比'
            is_warn = True
        # 要提取的位置（-1=最后一行，-2=倒数第二行，...，-27=倒数第27行）
        target_positions = [-1, -2, -3, -4, -5, -6, -27]

        # 按位置提取（.iloc 支持负位置）
        result = data.iloc[target_positions]
        return [title,content,result.to_html(),is_warn]

    def get_days_compare_hour_data(self):

        # 今天截止上个小时数据和前三天数据对比
        sql ="""
        SELECT activate_date,uniqCombined(oaid) as cnt
            FROM "db_cfx"."t_data_user_cpa_active_final" 
            where activate_date>today()-%(compare_days)s and toHour(activate_time)<toHour(now())
             group by activate_date 
             order by activate_date
        """
        params = {
            'compare_days': 4
        }
        result = self.client.query(sql,params)
        return result

    def get_active_final_hour_data(self):

        sql="""
        SELECT activate_date,toHour(activate_time) as hour,uniqCombined(oaid) as cnt
        FROM "db_cfx"."t_data_user_cpa_active_final" 
        where activate_date>today()-%(compare_days)s
         group by activate_date,toHour(activate_time)
         order by activate_date,toHour(activate_time)
        """

        params = {
            'compare_days': 3
        }

        result = self.client.query(sql,params)
        return result

    def get_table_update_time(self):
        sql="""
        select 'log' as table,toDateTime(max(base_create_time)) + INTERVAL 8 HOUR  as data_lastest_time, max(stored_time ) as update_time 
            from db_cfx.t_log_base_data_standard_active where base_create_date=today() 
            UNION all
            select 'active' as table,toDateTime(max(activate_time )) + INTERVAL 8 HOUR  as data_lastest_time,max(create_time ) as update_time 
            from db_cfx.t_data_user_cpa_active where activate_date=today() 
            UNION all
            select 'final' as table,toDateTime(max(activate_time )) + INTERVAL 8 HOUR  as data_lastest_time,max(create_time ) as update_time 
            from db_cfx.t_data_user_cpa_active_final where activate_date=today() 
        """
        params = { }
        result = self.client.query(sql,params)

        return result


# 使用示例
if __name__ == "__main__":


    client = HourData()
    data = client.gen_update_time_table()
    print(data)